package com.wutu.nbyjk8.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.excel.util.StringUtils;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.wutu.nbyjk8.domain.DataSourceManage;
import com.wutu.nbyjk8.mapper.DataSourceManageMapper;
import com.wutu.nbyjk8.vo.FiledsVO;
import lombok.extern.slf4j.Slf4j;

import java.util.List;
import java.util.Map;
import java.util.UUID;

@Slf4j
public class ExcelListener extends AnalysisEventListener<Map<String, String>> {

    private String id;

    private DataSourceManageMapper dataSourceManageMapper;

    public ExcelListener(String id, DataSourceManageMapper dataSourceManageMapper) {
        this.id = id;
        this.dataSourceManageMapper = dataSourceManageMapper;
    }

    /**
     * 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 100;
    private List<Map<String, String>> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

    @Override
    public void invoke(Map<String, String> data, AnalysisContext context) {
        log.info("解析到一条数据:{}", JSON.toJSONString(data));
        cachedDataList.add(data);
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();
        log.info("所有数据解析完成！");
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        DataSourceManage dataSourceManage = dataSourceManageMapper.selectById(id);
        List<FiledsVO> filedsVOS = JSONObject.parseArray(dataSourceManage.getFileds(), FiledsVO.class);
        StringBuilder sql = new StringBuilder("insert into ");
        sql.append("\"fuse\".");
        sql.append("\"");
        sql.append(dataSourceManage.getEName());
        sql.append("\" ");
        sql.append(" (");
        sql.append("\"id\" ");
        for (FiledsVO filedsVO : filedsVOS) {
            sql.append(",");
            sql.append("\"");
            sql.append(filedsVO.getName());
            sql.append("\" ");
        }
        sql.append(" )");
        sql.append("VALUES ");
        for (Map<String, String> map : cachedDataList) {
            sql.append(" ('");
            sql.append(UUID.randomUUID().toString());
            sql.append("'");
            for (Map.Entry<String, String> entry : map.entrySet()) {
                sql.append(",");
                if (StringUtils.isNotBlank(entry.getValue())) {
                    sql.append("'");
                    sql.append(entry.getValue());
                    sql.append("'");
                } else {
                    sql.append("null");
                }
            }
            sql.append(" )");
            sql.append(",");
        }
        String string = sql.toString();
        String substring = string.substring(0, sql.length() - 1);
        substring = substring + ";";
        dataSourceManageMapper.createable(substring);
        if (substring.contains("\"lon\"") && substring.contains("\"lat\"")) {
            dataSourceManageMapper.updatePoi("\"fuse\"." + dataSourceManage.getEName());
        }
        log.info("{}条数据，开始存储数据库！", this.cachedDataList.size());
        log.info("存储数据库成功！");
    }
}



